package com.lwlk.zdk.controller.Vehicle;


import com.lwlk.zdk.annotation.JwtIgnore;
import com.lwlk.zdk.mapper.T.*;
import com.lwlk.zdk.enums.RetCode;
import com.lwlk.zdk.mapper.pw.pwVehicleMapper;
import com.lwlk.zdk.model.Excel.ErrorMessage;
import com.lwlk.zdk.model.T.TDisDictionary;
import com.lwlk.zdk.model.T.TDisEnterprisePlatform;
import com.lwlk.zdk.model.T.TDisPlatform;
import com.lwlk.zdk.model.pw.pwVehicle;
import com.lwlk.zdk.model.T.TDisVehicle;
import com.lwlk.zdk.util.POIUtil;
import com.lwlk.zdk.util.Response;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Api(value = "车辆信息的excel")
@RestController
@RequestMapping(value = "/vehicleExcel")
public class ExcelVehicleController {


    @Autowired
    private pwVehicleMapper pwVehicleMapper;
    @Autowired
    private TDisVehicleMapper disVehicleMapper;
    @Autowired
    private TDisDictionaryMapper disDictionaryMapper;
    @Autowired
    private TRegionMapper regionMapper;
    @Autowired
    private TDisEnterpriseMapper disEnterpriseMapper;
    @Autowired
    TDisVehicleMapper tDisVehicleMapper;
    @Autowired
    TDisPlatformMapper disPlatformMapper;
    @Autowired
    TDisPlatformMapper tDisPlatformMapper;


    @ApiOperation(value = "车辆信息excel表格导入接口")
    @RequestMapping(value = "/IntoExcel",method = RequestMethod.POST)
    @JwtIgnore
    public Response IntoExcel(MultipartFile file) throws Exception {

        Response response=new Response();
        int errorCount = 0;/* 初始化错误条数 */
        int successCount = 0;/* 初始化错误条数 */
        List<ErrorMessage> errorMessages =new ArrayList<>();
        ErrorMessage errorMessage=null;
        try {
            Workbook workbook= POIUtil.getWorkBook(file);
            System.out.println(workbook);
            String fileName = file.getOriginalFilename();
            if(fileName.endsWith("xls")){
                // workbook = new HSSFWorkbook(is);
                Sheet shto = workbook.getSheetAt(0);
                for (Row row : shto) {
                    if (row.getRowNum() < 1) {
                        continue;
                    }
                    String vehicleLicenseColorName=row.getCell(1).getStringCellValue();//车牌颜色
                    String administrationOrgniaztion=row.getCell(2).getStringCellValue();//管辖地区
                    String industryName=row.getCell(3).getStringCellValue();//所属行业名称
                    String regionCodeName=row.getCell(4).getStringCellValue();//所属地区
                    String vehicleType=row.getCell(5).getStringCellValue();//车辆类型
                    String enterpriseName=row.getCell(6).getStringCellValue();//业户名称
                    String netStatus=row.getCell(7).getStringCellValue();//入网状态
                    Integer netStatusInt=null;
                    Integer enterpriseId=null;
                    if (enterpriseName!=null){
                        List<Map>enterpriseNameList=disEnterpriseMapper.selectEnterpriseNameList(enterpriseName);
                        enterpriseId= Integer.valueOf(String.valueOf(enterpriseNameList.get(0).get("enterprise_id")));
                    }
                    if (netStatus.equals("已入网")){
                        netStatusInt=1;
                    }else {
                        netStatusInt=0;
                    }
                    Short vehicleLicenseColor=null;
                    List<Map<String,Object>>dictionary=disDictionaryMapper.selectAll(null,vehicleLicenseColorName,null);//车牌颜色
                    if (dictionary.size()>0){
                        vehicleLicenseColor= Short.valueOf(String.valueOf(dictionary.get(0).get("itemValue")));
                    }else {
                        vehicleLicenseColor=null;
                    }
                    administrationOrgniaztion=administrationOrgniaztion.substring(0,administrationOrgniaztion.length()-3);
                    Integer administrationOrgniaztionCode =regionMapper.selectByCodeName(administrationOrgniaztion);
                    List<Map<String,Object>>industry=disDictionaryMapper.selectAll(null,industryName,null);
                    Integer industryCode=null;//所属行业
                    if (industry.size()>0){
                        industryCode= Integer.valueOf(String.valueOf(industryName));
                    }else {
                        industryCode=null;
                    }
                    Integer vehicleTypeCode=null;//车辆类型
                    List<Map<String,Object>>vehicleTypeName=disDictionaryMapper.selectAll(null,vehicleType,null);
                    if (vehicleTypeName.size()>0){
                        vehicleTypeCode= Integer.valueOf(String.valueOf(dictionary.get(0).get("itemValue")));
                    }else {
                        vehicleTypeCode=null;
                    }
                    Integer regionCode =regionMapper.selectByCodeName(regionCodeName);//所属地区
                    if (row.getCell(0).getStringCellValue()!=null){//如果车牌号不为空
                        List<TDisVehicle> disVehicles = disVehicleMapper.findUserByUsername(row.getCell(0).getStringCellValue());
                        if (disVehicles.size()==0){
                            //创建实体类
                            TDisVehicle disVehicle=new TDisVehicle();
                            Integer vehicleId=tDisVehicleMapper.selectMaxVehicleId()+1;
                            disVehicle.setVehicleId(vehicleId);
                            disVehicle.setVehicleNumber(row.getCell(0).getStringCellValue());//车牌号
                            disVehicle.setVehicleLicenseColor(vehicleLicenseColor);//车牌颜色
                            disVehicle.setAdministrationOrgniaztion(String.valueOf(administrationOrgniaztionCode));//管辖地区
                            disVehicle.setIndustryId(industryCode);//所属行业
                            disVehicle.setRegionCode(regionCode);//所属地区
                            disVehicle.setVehicleType(vehicleTypeCode);//车辆类型
                            disVehicle.setEnterpriseId(enterpriseId);//业户id
                            disVehicle.setNetStatus(netStatusInt);
                            disVehicle.setSource(1);
                            disVehicle.setDelFlag((short) 1);
                            disVehicleMapper.insertSelective(disVehicle);
                            successCount++;/* 成功条数统计 */
                        }else {
                            /* 封装错误信息 */
                            errorMessage=new ErrorMessage();
                            errorMessage.setVehicleNumber(row.getCell(0).getStringCellValue());
                            errorMessage.setVehicleLicenseColor(row.getCell(1).getStringCellValue());
                            errorMessage.setAdministrationOrgniaztion(row.getCell(2).getStringCellValue());
                            errorMessage.setIndustry(row.getCell(3).getStringCellValue());
                            errorMessage.setMessage("车牌号信息已存在!(请修改后删除此提示信息重新导入)");
                            errorMessages.add(errorMessage);
                            errorCount++;/* 用户名重复条数统计 */
                        }
                    }else {
                        /* 封装错误信息 */
                        errorMessage=new ErrorMessage();
                        errorMessage = new ErrorMessage();
                        errorMessage.setMessage("导入数据存中在空数据!(请修改后删除此提示信息重新导入)");
                        errorMessages.add(errorMessage);
                        errorCount++;/* 数据有空的项统计 */
                    }
                }
            }
            if(fileName.endsWith("xlsx")){
                //  workbook = new XSSFWorkbook(is);
                Sheet shto = workbook.getSheetAt(0);
                for (Row row : shto) {
                    if (row.getRowNum() < 1) {
                        continue;
                    }
                    String vehicleLicenseColorName=row.getCell(1).getStringCellValue();//车牌颜色
                    String administrationOrgniaztion=row.getCell(2).getStringCellValue();//管辖地区
                    String industryName=row.getCell(3).getStringCellValue();//所属行业名称
                    String regionCodeName=row.getCell(4).getStringCellValue();//所属地区
                    String vehicleType=row.getCell(5).getStringCellValue();//车辆类型
                    String enterpriseName=row.getCell(6).getStringCellValue();//业户名称
                    String netStatus=row.getCell(7).getStringCellValue();//入网状态
                    Integer netStatusInt=null;
                    Integer enterpriseId=null;
                    if (enterpriseName!=null){
                        List<Map>enterpriseNameList=disEnterpriseMapper.selectEnterpriseNameList(enterpriseName);
                        enterpriseId= Integer.valueOf(String.valueOf(enterpriseNameList.get(0).get("enterprise_id")));
                    }
                    if (netStatus.equals("已入网")){
                        netStatusInt=1;
                    }else {
                        netStatusInt=0;
                    }
                    Short vehicleLicenseColor=null;
                    List<Map<String,Object>>dictionary=disDictionaryMapper.selectAll(null,vehicleLicenseColorName,null);//车牌颜色
                    if (dictionary.size()>0){
                        vehicleLicenseColor= Short.valueOf(String.valueOf(dictionary.get(0).get("itemValue")));
                    }else {
                        vehicleLicenseColor=null;
                    }
                    administrationOrgniaztion=administrationOrgniaztion.substring(0,administrationOrgniaztion.length()-3);//管辖机构名称从0截取到字节长度的后三位
                    Integer administrationOrgniaztionCode =regionMapper.selectByCodeName(administrationOrgniaztion);
                    List<Map<String,Object>>industry=disDictionaryMapper.selectAll(null,industryName,null);
                    Integer industryCode=null;//所属行业
                    if (industry.size()>0){
                        industryCode= Integer.valueOf(String.valueOf(industry.get(0).get("itemValue")));
                    }else {
                        industryCode=null;
                    }
                    Integer vehicleTypeCode=null;//车辆类型
                    List<Map<String,Object>>vehicleTypeName=disDictionaryMapper.selectAll(null,vehicleType,null);
                    if (vehicleTypeName.size()>0){
                        vehicleTypeCode= Integer.valueOf(String.valueOf(vehicleTypeName.get(0).get("itemValue")));
                    }else {
                        vehicleTypeCode=null;
                    }
                    Integer regionCode =regionMapper.selectByCodeName(regionCodeName);//所属地区
                    if (row.getCell(0).getStringCellValue()!=null){//如果车牌号不为空
                        List<TDisVehicle> disVehicles = disVehicleMapper.findUserByUsername(row.getCell(0).getStringCellValue());
                        if (disVehicles.size()==0){
                            //创建实体类
                            TDisVehicle disVehicle=new TDisVehicle();
                            Integer vehicleId=tDisVehicleMapper.selectMaxVehicleId()+1;
                            disVehicle.setVehicleId(vehicleId);
                            disVehicle.setVehicleNumber(row.getCell(0).getStringCellValue());//车牌号
                            disVehicle.setVehicleLicenseColor(vehicleLicenseColor);//车牌颜色
                            disVehicle.setAdministrationOrgniaztion(String.valueOf(administrationOrgniaztionCode));//管辖地区
                            disVehicle.setIndustryId(industryCode);//所属行业
                            disVehicle.setRegionCode(regionCode);//所属地区
                            disVehicle.setVehicleType(vehicleTypeCode);//车辆类型
                            disVehicle.setEnterpriseId(enterpriseId);//业户id
                            disVehicle.setNetStatus(netStatusInt);
                            disVehicle.setDelFlag((short) 1);
                            disVehicle.setSource(1);
                            disVehicleMapper.insertSelective(disVehicle);
                            successCount++;/* 成功条数统计 */
                        }else {
                            /* 封装错误信息 */
                            errorMessage=new ErrorMessage();
                            errorMessage.setVehicleNumber(row.getCell(0).getStringCellValue());
                            errorMessage.setVehicleLicenseColor(row.getCell(1).getStringCellValue());
                            errorMessage.setAdministrationOrgniaztion(row.getCell(2).getStringCellValue());
                            errorMessage.setIndustry(row.getCell(3).getStringCellValue());
                            errorMessage.setMessage("车牌号信息已存在!(请修改后删除此提示信息重新导入)");
                            errorMessages.add(errorMessage);
                            errorCount++;/* 用户名重复条数统计 */
                        }
                    }else {
                        /* 封装错误信息 */
                        errorMessage=new ErrorMessage();
                        errorMessage = new ErrorMessage();
                        errorMessage.setMessage("导入数据存中在空数据!(请修改后删除此提示信息重新导入)");
                        errorMessages.add(errorMessage);
                        errorCount++;/* 数据有空的项统计 */
                    }
                }
            }
            response.setCode(RetCode.SUCCESS);
            response.setMsg("执行成功");
        }catch (Exception e){
            e.printStackTrace();
            response.setCode(RetCode.REQUEST_ERROR);
            response.setMsg("所填信息有为空的信息,请重新导入");
            return response;
        }finally {
            if (errorMessages!=null) {
               response.setData(errorMessages);
            }

        }
        return response;
    }






    @ApiOperation(value = "接入平台导入")
    @RequestMapping(value = "/IntoJoinPlatformExcel",method = RequestMethod.POST)
    @JwtIgnore
    public Response getIntoJoinPlatform(MultipartFile file) {
        Response response=new Response();
        int errorCount = 0;/* 初始化错误条数 */
        int successCount = 0;/* 初始化错误条数 */
        List<ErrorMessage> errorMessages =new ArrayList<>();
        ErrorMessage errorMessage=null;
        try {
            Workbook workbook= POIUtil.getWorkBook(file);
            System.out.println(workbook);
            String fileName = file.getOriginalFilename();
            if(fileName.endsWith("xls")){
                // workbook = new HSSFWorkbook(is);
                Sheet shto = workbook.getSheetAt(0);
                for (Row row : shto) {
                    if (row.getRowNum() < 2) {
                        continue;
                    }
                    String platformName=row.getCell(0).getStringCellValue();//平台名称
                    String createTime=row.getCell(1).getStringCellValue();//创建时间
                    String linkman=row.getCell(2).getStringCellValue();//平台联系人
                    String platformType=row.getCell(3).getStringCellValue();//平台类型
                    String assessPeriod=platformType.equals("地市级政府监管平台")?"0":(platformType.equals("非经营性企业监控平台")?"1": String.valueOf(platformType.equals("经营性企业监控平台")));
                    String bingIP=row.getCell(4).getStringCellValue();//绑定IP
                    String accessCode=row.getCell(5).getStringCellValue();//接入码
                    SimpleDateFormat sdf=new SimpleDateFormat();
                    if (row.getCell(0).getStringCellValue()!=null){//如果平台名不为空
                        List<Map> platformIdsList= disPlatformMapper.selectPlatformIdsList(row.getCell(1).getStringCellValue());
                        if (platformIdsList.size()==0){
                            //创建实体类
                            TDisPlatform disPlatform=new TDisPlatform();
                            Integer platformId=tDisPlatformMapper.selectMaxPlatformId()+1;
                            disPlatform.setPlatformId(platformId);
                            disPlatform.setPlatformName(platformName);
                            disPlatform.setCreateDate(sdf.parse(createTime));
                            disPlatform.setLinkman(linkman);
                            disPlatform.setPlatformType(Integer.valueOf(assessPeriod));
                            disPlatform.setPlatformIp(bingIP);
                            disPlatform.setAccessCode(accessCode);
                            disPlatform.setDelFlag((short) 1);
                            int i=tDisPlatformMapper.insertSelective(disPlatform);
                            successCount++;/* 成功条数统计 */
                        }else {
                            /* 封装错误信息 */
                            errorMessage=new ErrorMessage();
                            errorMessage.setVehicleNumber(row.getCell(0).getStringCellValue());
                            errorMessage.setVehicleLicenseColor(row.getCell(1).getStringCellValue());
                            errorMessage.setAdministrationOrgniaztion(row.getCell(2).getStringCellValue());
                            errorMessage.setIndustry(row.getCell(3).getStringCellValue());
                            errorMessage.setMessage("平台名称已存在!(请修改后删除此提示信息重新导入)");
                            errorMessages.add(errorMessage);
                            errorCount++;/* 用户名重复条数统计 */
                        }
                    }else {
                        /* 封装错误信息 */
                        errorMessage = new ErrorMessage();
                        errorMessage.setMessage("导入数据存中在空数据!(请修改后删除此提示信息重新导入)");
                        errorMessages.add(errorMessage);
                        errorCount++;/* 数据有空的项统计 */
                    }
                }
            }
            if(fileName.endsWith("xlsx")){
                //  workbook = new XSSFWorkbook(is);
                Sheet shto = workbook.getSheetAt(0);
                for (Row row : shto) {
                    if (row.getRowNum() < 1) {
                        continue;
                    }
                    String platformName=row.getCell(0).getStringCellValue();//平台名称
                    String createTime=row.getCell(1).getStringCellValue();//创建时间
                    String linkman=row.getCell(2).getStringCellValue();//平台联系人
                    String platformType=row.getCell(3).getStringCellValue();//平台类型
                    String assessPeriod=platformType.equals("地市级政府监管平台")?"0":(platformType.equals("非经营性企业监控平台")?"1": String.valueOf(platformType.equals("经营性企业监控平台")));
                    String bingIP=row.getCell(4).getStringCellValue();//绑定IP
                    String accessCode=row.getCell(5).getStringCellValue();//接入码
                    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    if (row.getCell(0).getStringCellValue()!=null){//如果平台名称不为空
                        List<Map> platformIdsList= disPlatformMapper.selectPlatformIdsList(row.getCell(0).getStringCellValue());
                        if (platformIdsList.size()==0){
                            //创建实体类
                            TDisPlatform disPlatform=new TDisPlatform();
                            Integer platformId=tDisPlatformMapper.selectMaxPlatformId()+1;
                            disPlatform.setPlatformId(platformId);
                            disPlatform.setPlatformName(platformName);
                            disPlatform.setCreateDate(sdf.parse(createTime));
                            disPlatform.setLinkman(linkman);
                            disPlatform.setPlatformType(Integer.valueOf(assessPeriod));
                            disPlatform.setPlatformIp(bingIP);
                            disPlatform.setAccessCode(accessCode);
                            disPlatform.setDelFlag((short) 1);
                            int i=tDisPlatformMapper.insertSelective(disPlatform);
                            successCount++;/* 成功条数统计 */
                        }else {
                            /* 封装错误信息 */
                            errorMessage=new ErrorMessage();
                            errorMessage.setVehicleNumber(row.getCell(0).getStringCellValue());
                            errorMessage.setVehicleLicenseColor(row.getCell(1).getStringCellValue());
                            errorMessage.setAdministrationOrgniaztion(row.getCell(2).getStringCellValue());
                            errorMessage.setIndustry(row.getCell(3).getStringCellValue());
                            errorMessage.setMessage("平台名称已存在!(请修改后删除此提示信息重新导入)");
                            errorMessages.add(errorMessage);
                            errorCount++;/* 用户名重复条数统计 */
                        }
                    }else {
                        /* 封装错误信息 */
                        errorMessage = new ErrorMessage();
                        errorMessage.setMessage("导入数据存中在空数据!(请修改后删除此提示信息重新导入)");
                        errorMessages.add(errorMessage);
                        errorCount++;/* 数据有空的项统计 */
                    }
                }
            }
            response.setCode(RetCode.SUCCESS);
            response.setMsg("执行成功");
        }catch (Exception e){
            e.printStackTrace();
            response.setMsg("所填信息有为空的信息,请重新导入");
            return response;
        }finally {
            if (errorMessages!=null) {
                response.setData(errorMessages);
            }

        }
        return response;
    }
    }
